﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace NhaSachDAO
{
    public class ChiTietHoaDonDAO
    {
        public static DataTable LoadChiTietHoaDon()
        {
            SqlConnection cnn = SQLconnection.Hamketnoi();
            SqlCommand cnd = new SqlCommand("select tensach as 'Tên sách',s.dongia as 'Đơn giá',ct.soluong as 'Số lượng' from ChiTietHoaDon ct, sach s where ct.masach=s.masach", cnn);
            cnd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter(cnd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public static DataTable LoadChiTietHoaDonMua(string mahd)
        {
            SqlConnection cnn = SQLconnection.Hamketnoi();
            SqlCommand cnd = new SqlCommand("select tensach as 'Tên sách',s.dongia as 'Đơn giá',ct.soluong as 'Số lượng',ct.thanhtien as 'Thành tiền' from ChiTietHoaDon ct, sach s where ct.masach=s.masach and ct.mahd=" + mahd + "", cnn);
            cnd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter(cnd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public static void ThemChiTietHoaDon(Int64 masach,string mahd,int soluong,string thanhtien)
        {
          
            {
                SqlConnection cnn = SQLconnection.Hamketnoi();
                SqlCommand cmd = new SqlCommand("Insert into ChiTietHoaDon(MaSach,MaHD,SoLuong,thanhtien) Values ( " + masach + "," + mahd + "," + soluong + ","+thanhtien+")", cnn);
                cmd.CommandType = CommandType.Text;
                cnn.Open();
                cmd.ExecuteNonQuery();
                cnn.Close();
                CapNhatChiTietHoaDon(masach, soluong);
            }
        }
        public static void CapNhatChiTietHoaDon(Int64 masach,  int soluong)
        {
            SqlConnection cnn = SQLconnection.Hamketnoi();
            SqlCommand cmd = new SqlCommand("Update sach set soluong = soluong-" + soluong +"  Where Masach=" + masach + "", cnn);
            cmd.CommandType = CommandType.Text;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        public static void ChuanBi(string mahd)
        {
            SqlConnection cnn = SQLconnection.Hamketnoi();
            SqlCommand cmd = new SqlCommand("Update HoaDon set tongtien = ( select sum(thanhtien) from chitiethoadon where mahd="+mahd+") where mahd ="+mahd+"", cnn);
            cmd.CommandType = CommandType.Text;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        public static void GoiHang(Int64 masach, string mahd, int soluong,string thanhtien)
        {
            ThemChiTietHoaDon(masach, mahd, soluong,thanhtien);

        }
    }
}
